import delimited using ${excel}/ADS.DE.csv, clear
split date, parse("-")

destring date1, force replace
destring date2, force replace
destring date3, force replace

ren date1 year
ren date2 month
ren date3 day

gen eventdate = mdy(month,day,year)
format eventdate %td
drop date
keep close eventdate month year
gen company = "Adidas"

save stockprice.dta, replace

import delimited using ${excel}/BAS.DE.csv, clear
split date, parse("-")

destring date1, force replace
destring date2, force replace
destring date3, force replace

ren date1 year
ren date2 month
ren date3 day

gen eventdate = mdy(month,day,year)
format eventdate %td
drop date
keep close eventdate month year
gen company = "BASF"

append using stockprice.dta
save stockprice.dta, replace

import delimited using ${excel}/BAYN.DE.csv, clear
split date, parse("-")

destring date1, force replace
destring date2, force replace
destring date3, force replace

ren date1 year
ren date2 month
ren date3 day

gen eventdate = mdy(month,day,year)
format eventdate %td
drop date
keep close eventdate month year
gen company = "Bayer"

append using stockprice.dta
save stockprice.dta, replace

import delimited using ${excel}/BEI.DE.csv, clear
split date, parse("-")

destring date1, force replace
destring date2, force replace
destring date3, force replace

ren date1 year
ren date2 month
ren date3 day

gen eventdate = mdy(month,day,year)
format eventdate %td
drop date
keep close eventdate month year
gen company = "Beiersdorf"

append using stockprice.dta
save stockprice.dta, replace

import delimited using ${excel}/BMW.DE.csv, clear
split date, parse("-")

destring date1, force replace
destring date2, force replace
destring date3, force replace

ren date1 year
ren date2 month
ren date3 day

gen eventdate = mdy(month,day,year)
format eventdate %td
drop date
keep close eventdate month year
gen company = "BMW"

append using stockprice.dta
save stockprice.dta, replace

import delimited using ${excel}/DAI.DE.csv, clear
split date, parse("-")

destring date1, force replace
destring date2, force replace
destring date3, force replace

ren date1 year
ren date2 month
ren date3 day

gen eventdate = mdy(month,day,year)
format eventdate %td
drop date
keep close eventdate month year
gen company = "Daimler"

append using stockprice.dta
save stockprice.dta, replace

import delimited using ${excel}/EOAN.DE.csv, clear
split date, parse("-")

destring date1, force replace
destring date2, force replace
destring date3, force replace

ren date1 year
ren date2 month
ren date3 day

gen eventdate = mdy(month,day,year)
format eventdate %td
drop date
keep close eventdate month year
gen company = "EON"

append using stockprice.dta
save stockprice.dta, replace

import delimited using ${excel}/FRE.DE.csv, clear
split date, parse("-")

destring date1, force replace
destring date2, force replace
destring date3, force replace

ren date1 year
ren date2 month
ren date3 day

gen eventdate = mdy(month,day,year)
format eventdate %td
drop date
keep close eventdate month year
gen company = "Fresenius"

append using stockprice.dta
save stockprice.dta, replace

import delimited using ${excel}/_CON.DE.csv, clear
split date, parse("-")

destring date1, force replace
destring date2, force replace
destring date3, force replace

ren date1 year
ren date2 month
ren date3 day

gen eventdate = mdy(month,day,year)
format eventdate %td
drop date
keep close eventdate month year
gen company = "Continental"

append using stockprice.dta
save stockprice.dta, replace

import delimited using ${excel}/HEN.DE.csv, clear
split date, parse("-")

destring date1, force replace
destring date2, force replace
destring date3, force replace

ren date1 year
ren date2 month
ren date3 day

gen eventdate = mdy(month,day,year)
format eventdate %td
drop date
keep close eventdate month year
gen company = "Henkel"

append using stockprice.dta
save stockprice.dta, replace

import delimited using ${excel}/IFX.DE.csv, clear
split date, parse("-")

destring date1, force replace
destring date2, force replace
destring date3, force replace

ren date1 year
ren date2 month
ren date3 day

gen eventdate = mdy(month,day,year)
format eventdate %td
drop date
keep close eventdate month year
gen company = "Infineon"

append using stockprice.dta
save stockprice.dta, replace

import delimited using ${excel}/LHA.DE.csv, clear
split date, parse("-")

destring date1, force replace
destring date2, force replace
destring date3, force replace

ren date1 year
ren date2 month
ren date3 day

gen eventdate = mdy(month,day,year)
format eventdate %td
drop date
keep close eventdate month year
gen company = "Lufthansa"

append using stockprice.dta
save stockprice.dta, replace

import delimited using ${excel}/LIN.DE.csv, clear
split date, parse("-")

destring date1, force replace
destring date2, force replace
destring date3, force replace

ren date1 year
ren date2 month
ren date3 day

gen eventdate = mdy(month,day,year)
format eventdate %td
drop date
keep close eventdate month year
gen company = "Linde"

append using stockprice.dta
save stockprice.dta, replace

import delimited using ${excel}/MRK.DE.csv, clear
split date, parse("-")

destring date1, force replace
destring date2, force replace
destring date3, force replace

ren date1 year
ren date2 month
ren date3 day

gen eventdate = mdy(month,day,year)
format eventdate %td
drop date
keep close eventdate month year
gen company = "Merck"

append using stockprice.dta
save stockprice.dta, replace

import delimited using ${excel}/PSM.DE.csv, varnames(1) clear 
split date, parse("-")

destring close, force replace
destring date1, force replace
destring date2, force replace
destring date3, force replace

ren date1 year
ren date2 month
ren date3 day

gen eventdate = mdy(month,day,year)
format eventdate %td
drop date
keep close eventdate month year
gen company = "ProSiebenSat"

append using stockprice.dta
save stockprice.dta, replace

import delimited using ${excel}/RWE.DE.csv, clear
split date, parse("-")

destring date1, force replace
destring date2, force replace
destring date3, force replace

ren date1 year
ren date2 month
ren date3 day

gen eventdate = mdy(month,day,year)
format eventdate %td
drop date
keep close eventdate month year
gen company = "RWE"

append using stockprice.dta
save stockprice.dta, replace

import delimited using ${excel}/SIE.DE.csv, clear
split date, parse("-")

destring date1, force replace
destring date2, force replace
destring date3, force replace

ren date1 year
ren date2 month
ren date3 day

gen eventdate = mdy(month,day,year)
format eventdate %td
drop date
keep close eventdate month year
gen company = "Siemens"

append using stockprice.dta
save stockprice.dta, replace

import delimited using ${excel}/TKA.DE.csv, clear
split date, parse("-")

destring date1, force replace
destring date2, force replace
destring date3, force replace

ren date1 year
ren date2 month
ren date3 day

gen eventdate = mdy(month,day,year)
format eventdate %td
drop date
keep close eventdate month year
gen company = "Thyssenkrupp"

append using stockprice.dta
save stockprice.dta, replace

import delimited using ${excel}/VNA.DE.csv, clear
split date, parse("-")

destring date1, force replace
destring date2, force replace
destring date3, force replace

ren date1 year
ren date2 month
ren date3 day

gen eventdate = mdy(month,day,year)
format eventdate %td
drop date
keep close eventdate month year
gen company = "Vonovia"

append using stockprice.dta
save stockprice.dta, replace

import delimited using ${excel}/VOW.DE.csv, clear
split date, parse("-")

destring date1, force replace
destring date2, force replace
destring date3, force replace

ren date1 year
ren date2 month
ren date3 day

gen eventdate = mdy(month,day,year)
format eventdate %td
drop date
keep close eventdate month year
gen company = "Volkswagen"

append using stockprice.dta

gen 	quarter = 1 if month<=3
replace quarter = 2 if month>3 & month<=6
replace quarter = 3 if month>6 & month<=9
replace quarter = 4 if month>9

save stockprice.dta, replace
********************************************************************************
import excel ${excel}/locationplants.xlsx, sheet("Foglio1") firstrow clear
save locationplants.dta, replace

use locationplants.dta, clear
tokenize "Adidas BASF Bayer Beiersdorf BMW Continental Daimler Lufthansa EON Fresenius Henkel Infineon Linde Merck ProSiebenSat RWE Siemens Thyssenkrupp Volkswagen Vonovia", parse(" ")
forvalues v1=1(1)20{
destring ``v1'', force replace
}
keep Adidas BASF Bayer Beiersdorf BMW Continental Daimler Henkel Infineon Linde Merck Siemens Thyssenkrupp Volkswagen
tokenize "Adidas BASF Bayer Beiersdorf BMW Continental Daimler Henkel Infineon Linde Merck Siemens Thyssenkrupp Volkswagen", parse(" ")
forvalues v1=1(1)14{
egen c`v1'=count(``v1'')
}
********************************************************************************
tokenize "Adidas BASF Bayer Beiersdorf BMW Continental Daimler Lufthansa EON Fresenius Henkel Infineon Linde Merck ProSiebenSat RWE Siemens Thyssenkrupp Volkswagen Vonovia", parse(" ")
forvalues v1=1(1)20{
di "``v1''"
use locationplants.dta, clear 

qui: keep ``v1''
qui: destring ``v1'', force replace
qui: drop if ``v1'' == .
qui: duplicates drop
qui: ren ``v1'' krs
qui: gen company = "``v1''"
qui: merge 1:m krs using ${orig}/employmentlongdata_krs_SA.dta
qui: drop if _merge!=3
qui: drop _merge
qui: xtset krs date
qui: gen growth_rate = ln(employment) - ln(l.employment)

qui: keep growth_rate krs year quarter date company
qui: reshape wide growth_rate, i(date) j(krs)
merge 1:m year quarter company using stockprice.dta

drop if _merge!=3
drop _merge
sort eventdate
gen idtime = _n
tsset idtime
gen growth_stock = ln(close)-ln(l.close)

by year quarter, sort: egen peakchange = max(growth_stock)
by year quarter, sort: egen trouchange = min(growth_stock)
by year quarter, sort: egen meanchange = mean(growth_stock)
by year quarter, sort: gen counter = _n
by year quarter, sort: gen count = _N
gen aux = close if counter == count
by year quarter, sort: egen close_qrt = mean(aux)

keep company growth_rate* trouchange peakchange meanchange year quarter close_qrt
duplicates drop

if "``v1''" == "Adidas"{ 
save temp.dta, replace
}
else{
append using temp.dta
save temp.dta, replace
}
}

use temp.dta, clear
save narrative.dta, replace

use narrative.dta, clear

egen idcompany = group(company)
egen date = group(year quarter)
egen id   = group(idcompany date)
reshape long growth_rate, i(id) j(krs)
egen idplant = group(idcompany krs)
drop if growth_rate == .

sum idcompany
global max = `r(max)'-1
gen eventdate = yq(year,quarter)
format eventdate %tq

forv v = 1(1)$max {
preserve
di `v'
keep if idcompany == `v'
qui: keep idcompany close_qrt eventdate
qui: duplicates drop
tsset eventdate
keep if close_qrt != . 
seasonallyadjust close_qrt, gen(close_sa) ar(1) ma(1) x11 multiplicative replace
qui: save temp`v'.dta, replace

restore
}

preserve

use temp1.dta, clear
cap erase temp1.dta
forv v = 2(1)$max {
append using temp`v'.dta
cap erase temp`v'.dta
}
sum
save tempsa.dta, replace

restore

merge m:1 eventdate idcompany using tempsa.dta
drop _merge

replace close_sa = close_qrt if company == "Vonovia"

xtset idplant eventdate
gen growth_stock = ln(close_sa) - ln(l.close_sa)

tokenize "Adidas BASF Bayer Beiersdorf BMW Continental Daimler Lufthansa EON Fresenius Henkel Infineon Linde Merck ProSiebenSat RWE Siemens Thyssenkrupp Volkswagen Vonovia", parse(" ")
forvalues v1=1(1)20{
preserve
di "``v1''"
keep if company == "``v1''"
gen correlation = .
gen ttest = .
egen idkrs = group(krs)
sum idkrs
forv x = 1(1)`r(max)'{
corr growth_rate growth_stock if idkrs==`x'
qui: replace correlation = `r(rho)' if idkrs==`x'
qui: corr growth_rate growth_stock if idkrs==`x'
qui: replace ttest = correlation*(`r(N)'-2)^.5/(1-correlation^2)^.5
}

if "``v1''" == "Adidas"{ 
save temp.dta, replace
}
else{
append using temp.dta
save temp.dta, replace
}

restore
}

use temp.dta, clear
keep if ttest>1.64

drop id
egen id = group(company krs)
xtset id eventdate
gen ma_empl = (f.growth_rate + growth_rate + l.growth_rate)/3
gen ma_stock = (f.growth_stock + growth_stock + l.growth_stock)/3
sum id 
global max = `r(max)'-1
forv i = 1(1)$max {
twoway (tsline ma_stock if id==`i', yaxis(1) lcolor(black) lpattern(dash)) (tsline ma_empl if id==`i', yaxis(2) lcolor(black)), graphregion(color(white)) legend(label(1 "m.a. of stock price growth") label(2 "m.a. of employment growth")) ytitle("", axis(1)) ytitle("", axis(2)) xtitle("Time")
graph export ${graphs}/correlation`i'.png, replace
}
